--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Model Version 0.0.0.0

--This SQL is generated for the table 'REGION'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionDelete]
(
	@Original_region_id [Int]
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[REGION] 
WHERE 
	[region_id] = @Original_region_id ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionSelectByRegionPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionSelectByRegionPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_RegionSelectByRegionPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]
FROM 
[REGION]
WHERE

	[REGION].[region_id] IN (SELECT [region_id] 
											FROM OpenXML(@hDoc, '//Item', 2) 
											WITH ([region_id] [Char] (36) 'region_id')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionSelectByRegionSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionSelectByRegionSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionSelectByRegionSinglePk]
(
	@region_id [Int]
)
AS

SELECT 
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]

FROM
[REGION]
WHERE 
[REGION].[region_id] = @region_id 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_RegionInsert]
(
	@name [VarChar] (50) = default,
	@region_id [Int] = default,
	@created_date [DateTime],
	@created_by [Varchar] (50),
	@modified_by [Varchar] (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
IF (@region_id < 0) SET @region_id = NULL;
if ((@region_id IS NULL))
BEGIN
INSERT INTO [REGION]
(
	[name],
	[created_date],
	[created_by],
	[modified_date],
	[modified_by]
)
VALUES
(
	@name,
	@created_date,
	@created_by,
	@created_date,
	@modified_by
);
END
ELSE
BEGIN
SET identity_insert [REGION] on
INSERT INTO [REGION]
(
	[region_id],
	[name],
	[created_date],
	[created_by],
	[modified_date],
	[modified_by]
)
VALUES
(
	@region_id,
	@name,
	@created_date,
	@created_by,
	@created_date,
	@modified_by
);
SET identity_insert [REGION] off
END


SELECT 
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]

FROM
[REGION]
WHERE
	[REGION].[region_id] = SCOPE_IDENTITY();
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionPagingSelect]
(
	@page [Int], -- page number selected by the user
	@pageSize [Int], -- number of items on the page
	@orderByColumn [Varchar] (100), -- name of column to order things by
	@ascending [Bit], -- order column ascending or descending
	@filter [Varchar] (100) = null, -- filter statement passed in to determine like criteria on order by column
	@count [Int] out -- number of items in the collection
)
AS

SET NOCOUNT ON;

CREATE TABLE #tmpTable
(
	[region_id] [Int]
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[region_id]
)
SELECT
	[REGION].[region_id]
FROM
[REGION]
WHERE
	(@orderByColumn = 'name' and (((@filter is null) or ([REGION].[name] is null)) or (@filter is not null and [REGION].[name] LIKE @filter)))
or
	(@orderByColumn = 'region_id' and (((@filter is null) or ([REGION].[region_id] is null)) or (@filter is not null and [REGION].[region_id] = @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'name' THEN [REGION].[name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'name' THEN [REGION].[name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'region_id' THEN [REGION].[region_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'region_id' THEN [REGION].[region_id] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[REGION]
WHERE
	(@orderByColumn = 'name' and (((@filter is null) or ([REGION].[name] is null)) or (@filter is not null and [REGION].[name] LIKE @filter)))
or
	(@orderByColumn = 'region_id' and (((@filter is null) or ([REGION].[region_id] is null)) or (@filter is not null and [REGION].[region_id] = @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [REGION] ON #tmpTable.[region_id] = [REGION].[region_id]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'name' THEN [REGION].[name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'name' THEN [REGION].[name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'region_id' THEN [REGION].[region_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'region_id' THEN [REGION].[region_id] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionUpdate]
(
	@name [VarChar] (50),
	@modified_by [Varchar] (50),
	@modified_date [DateTime] = null,
	@Original_region_id [Int],
	@Original_time_stamp timestamp
)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[REGION] 
SET
	[name] = @name,
	[modified_by] = @modified_by,
	[modified_date] = @modified_date

WHERE
	[REGION].[region_id] = @Original_region_id AND
	[REGION].[time_stamp] = @Original_time_stamp


SELECT
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]
FROM 
[REGION]
WHERE
	[REGION].[region_id] = @Original_region_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionSelectByCreatedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]
FROM
[REGION]
WHERE
((([REGION].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [REGION].[created_date])) AND 
((([REGION].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [REGION].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionSelectByModifiedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]
FROM
[REGION]
WHERE
((([REGION].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [REGION].[modified_date])) AND 
((([REGION].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [REGION].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_RegionSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_RegionSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_RegionSelect]
AS

SET NOCOUNT ON;

SELECT
	[REGION].[name],
	[REGION].[region_id],
	[REGION].[created_by],
	[REGION].[created_date],
	[REGION].[modified_by],
	[REGION].[modified_date],
	[REGION].[time_stamp]
FROM 
[REGION]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

